#coding:utf-8
__author__ = 'zhaoyoucai'

from bs4 import BeautifulSoup
import re
import MySQLdb
import datetime

import requests
import time



def LinkDB():
    conn=MySQLdb.connect(host='localhost',user='root',passwd='zyc880128',db='tradetrends',port=3306,charset='utf8')
    #conn=MySQLdb.connect(host='120.76.134.53',user='remote_user',passwd='tradetrends',db='erp_data',port=3306,charset='utf8')
    return conn


#ff = open('html/page001.html')

#html = ff.read()


def fetchItem(html,user_id):
    html = BeautifulSoup(html,'html.parser')

    item2 = html.find_all('tr','goodsId')
    item3 = html.find_all('table','tableIn')

    x = len(item3)
    m = 0
    for i in range(0,x):

        conn = LinkDB()
        cur=conn.cursor()

        sss = item3[i]

        goods = sss.find_all('tr')

        allsku = ''
        i = 0
        skunums = []
        for g in goods:
            img = g.img.get('src')
            kkk = g.find_all('a')
            if len(kkk)>1:
                sku = kkk[1].get_text()
            else:
                sku = kkk[0].get_text()

            num = g.span.get_text()

            usd = g.find("p", text=re.compile("USD"))
            eru = g.find("p", text=re.compile("EUR"))
            au = g.find("p", text=re.compile("AU"))
            if usd:
                unit = 'USD'
                price = usd.get_text()
            elif eru:
                unit = 'EUR'
                price = eru.get_text()
            elif au:
                unit = 'AU'
                price = au.get_text()
            price = price.split(' ')[1]
            c = g.find("p", text=re.compile("Color"))
            if c:
                color = c.get_text()
                color = color.split(u'：')[1]
                color = color.replace(' ','_')
            else:
                color = str(i)

            if color:
                suffix = color
            else:
                suffix = str(i)

            if sku in allsku:
                sku += '_%s'%suffix

            allsku += sku
            allsku += ','

            i +=1

            sql = 'INSERT INTO m_sku_price (SKU_PRICE_ID,SKU,SELL_PRICE,IMAGEURL)' \
                  ' VALUES (uuid(),"%s","%s","%s")'%(sku,price,img)

            try:
                cur.execute(sql)
                conn.commit()
            except Exception as e:
                conn.commit()

            skunums.append({'sku':sku,'num':num})


        allsku = allsku.strip(',')

        detail = sss.parent.parent


        detail.find('table','tableIn').decompose()

        tx = BeautifulSoup(str(detail),'html.parser')


        nx = tx.find_all('td')

        if len(nx)<1:
            break

        money = nx[1]   # 订单金额
        ct = nx[2]  # 国家
        od = nx[3]  # 订单号
        ot = nx[4].get_text()  # 订单时间

        total = money.find(text=re.compile("USD"))
        if total == None:
            total = money.find(text=re.compile("EUR"))
        if total == None:
            total = money.find(text=re.compile("AU"))
        total = total.split(' ')[1]

        cc = ct.find_all('span')
        cc = cc[1]
        cc = cc.get_text()
        cc = cc.replace(u'「','')
        cc = cc.replace(u'」','')
        cc = cc.strip()

        orderId = od.find_all('a')

        orderId = orderId[0].get_text()
        orderId = orderId.strip()

        ts = ot.split('\n')

        t1 = t2 = ''
        for t in ts:
            if t.find(u'下单') != -1:
                t1 = t.strip()
            elif t.find(u'发货') != -1:
                t2 = t.strip()

        t1 = t1.split(u'：')[1]
        t2 = t2.split(u'：')[1]

        if len(nx)>5:
            test = item2[m]

            line = test.find_all('td')

            tmp = line[0]

            DXMPJTRU = tmp.find('a','mLeft10').get_text()

            tmp = line[2]
            storeId = tmp.span.get_text()
            storeId = storeId.replace('\n','')
            storeId = storeId.replace('\t','')
            storeId = storeId.replace(u'「','')
            storeId = storeId.replace(u'」','')
            storeId = storeId.strip()
            storeId = storeId.split(u'：')[1]
            storeId = storeId.strip()

            lo = nx[5].get_text()  # 物流单号
            lo = lo.replace('\n','')
            lo = lo.replace('\t','')
            channel = lo.split(u'「')[0]
            track_num = lo.split(u'「')[1].replace(u'」','')

            #print('Channel:[%s]'%(channel))
            #print('TrackNum:[%s]'%track_num)

            m +=1

            sql='INSERT into `m_profit` (PROFIT_ID,TRACKNUM,ORDERID,SKU,ORDERPRICE,ORDERTIME,SENDTIME,COUNTRY,STOREID,CURRENCY,CREATE_DATE)' \
                ' values (uuid(),"%s","%s","%s","%s","%s","%s","%s","%s","%s",NOW())'%(track_num,orderId,allsku,total,t1,t2,cc,storeId,unit)

            #print(sql)

            try:
                cur.execute(sql)
                conn.commit()
            except Exception as e:
                print(e)

            try:
                sql = 'INSERT INTO `logistics` (tracking_number,send_time,destination,user_id,channel,delivery_time)' \
                      ' VALUES ("%s","%s","%s",%s,"%s",NOW())'%(track_num,t1,cc,str(user_id),channel)
                cur.execute(sql)
                conn.commit()
            except Exception as e:
                print(e)


            sql = 'SELECT PROFIT_ID from m_profit where TRACKNUM="%s"'%(track_num)

            cur.execute(sql)
            #conn.commit()

            profit_id =''
            for p in cur:
                profit_id = p[0]

            if profit_id:
                for item in skunums:
                    sql = 'INSERT INTO m_profit_sku (PROFIT_SKU_ID,PROFIT_ID,SKU,`NUMBER`)' \
                          ' VALUES (uuid(),"%s","%s",%s)'%(profit_id,item['sku'],item['num'])

                    cur.execute(sql)
                    conn.commit()
                    #print(sql)


        conn.close()
        #print('[%d]**************************************************'%(m))


def getdDXMPage(s,pageNo,pageSize,startDate,endDate):
    url = 'http://www.dianxiaomi.com/package/list.htm?pageNo=%d&pageSize=%d&shopId=-1&state=shipped&platform=&isSearch=1&searchType=orderId&authId=-1&startTime=%s&endTime=%s&country=&orderField=shipped_time&isVoided=0&ruleId=-1&sysRule=&applyType=&applyStatus=&printJh=-1&printMd=-1&commitPlatform=&productStatus=&jhComment=-1&storageId=0'%(pageNo,pageSize,startDate,endDate)
    r = s.get(url)

    return r.content


def loginDXM(account,password):
    URL_HOME = 'http://www.dianxiaomi.com/index.htm'
    URL_ADMIN_HOME = 'http://www.dianxiaomi.com/home.htm'
    URL_LOGIN = 'http://www.dianxiaomi.com/user/login.htm'
    URL_ORDER = 'http://www.dianxiaomi.com/order/index.htm'


    headers = {'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
               'Accept-Encoding': 'gzip, deflate, compress',
               'Accept-Language': 'zh-CN,zh;q=0.8',
               'Cache-Control': 'max-age=0',
               'Connection': 'keep-alive',
               'Referer': 'http://www.dianxiaomi.com/index.htm',
               'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.152 Safari/537.36'}

    s = requests.Session()
    s.headers.update(headers)

    s.get(URL_HOME)


    login_data={
        'account':account,
        'password':password,
        'remember':'remeber'
    }

    s.post(URL_LOGIN,data=login_data)

    ts = int(time.time())

    s.get('%s?ts=%d'%(URL_ADMIN_HOME,ts))

    s.get(URL_ORDER)

    return s


def getTotalandPage(html):

    rule = r'var totalSize = \'([0-9]*)\';'

    result = re.search(rule,html)

    if result.group(1):
        totalSize = int(result.group(1))
    else:
        totalSize = 0

    rule = r'options.totalPages	= \'([0-9]*)\';'
    result = re.search(rule,html)

    if result.group(1):
        totalPages = int(result.group(1))
    else:
        totalPages = 0

    return (totalSize,totalPages)